package com.rollcall;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DatabaseUtil {
    private static Properties props = new Properties();
    
    static {
        try (InputStream input = DatabaseUtil.class.getClassLoader()
                .getResourceAsStream("config.properties")) {
            props.load(input);
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            props.getProperty("db.url"),
            props.getProperty("db.username"),
            props.getProperty("db.password")
        );
    }

    public static List<Student> getStudentsInOrder() {
        List<Student> students = new ArrayList<>();

        String sql = "SELECT id, name, order_num, absence FROM students ORDER BY order_num";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setOrderNum(rs.getInt("order_num"));
                student.setAbsence(rs.getInt("absence")); // 新增
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return students;
    }
}
